﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using dotNails.CodeGen.Models;
using dotNails.CodeGen.Util;
using System.Data.SqlClient;
using System.Windows.Forms;
using System.Data;

namespace dotNails.Util {
	public class SmoUtil {
		public static Database GetSmoDatabase(Server server, string dbName) {
			Database smoDb = null;

			foreach (Database db in server.Databases) {
				if (db.Name == dbName) {
					smoDb = db;
				}
			}

			return smoDb;
		}

		public static Microsoft.SqlServer.Management.Smo.Table GetSmoTable(Database smoDb, string schemaName, string tableName) {
			Microsoft.SqlServer.Management.Smo.Table smoTable = null;

			foreach (Microsoft.SqlServer.Management.Smo.Table table in smoDb.Tables) {
				if (table.Name == tableName && table.Schema == schemaName) {
					smoTable = table;
				}
			}

			return smoTable;
		}

		public static List<Microsoft.SqlServer.Management.Smo.Table> GetAllTablesNotInDbml(DbmlData dbml) {
			List<Microsoft.SqlServer.Management.Smo.Table> tables = new List<Microsoft.SqlServer.Management.Smo.Table>();

			try {
				// Whip up an anonymous typed List
				var tableInDbml = new { SchemaName = "", TableName = "" };
				var tablesInDbml = (new[] { tableInDbml }).ToList();
				tablesInDbml.RemoveAt(0);

				// Store the schema and table names of all the tables in the dbml
				if (dbml.DB.Tables != null) {
					foreach (dotNails.CodeGen.Models.Table dbmlTable in dbml.DB.Tables) {
						string schemaName;
						string tableName;

						// Dbml mashes the schema and table name together, so we need to split them out
						DbmlUtil.GetSchemaAndNameFromFullName(dbmlTable.Name, out schemaName, out tableName);
						tablesInDbml.Add(new { SchemaName = schemaName, TableName = tableName });
					}
				}

				if (dbml.DB != null && dbml.DB.Connection != null && !string.IsNullOrEmpty(dbml.DB.Connection.ConnectionString)) {
					// Get the tables from the database
					SqlConnection conn = new SqlConnection(dbml.DB.Connection.ConnectionString);
					Server server = new Server(new ServerConnection(conn));
					server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedure), "IsSystemObject");

					foreach (Database db in server.Databases) {
						if (db.Name == dbml.DB.Name) {
							// We've got the right database, now filter out any table already in the dbml
							foreach (Microsoft.SqlServer.Management.Smo.Table smoTable in db.Tables) {
								if (!smoTable.IsSystemObject) {
									var dbmlTable = tablesInDbml.Find(t => t.TableName == smoTable.Name && t.SchemaName == smoTable.Schema);
									if (dbmlTable == null) {
										// Table not found in dbml, so add it to the list
										tables.Add(smoTable);
									}
								}
							}
						}
					}
				}
				else {
					throw new Exception("Connection string not found for " + dbml.Name);
				}
			}
			catch (Exception ex) {
				MessageBox.Show(ex.Message);
				throw ex;
			}

			return tables;
		}

		public static List<Microsoft.SqlServer.Management.Smo.StoredProcedure> GetAllStoredProceduresNotInDbml(DbmlData dbml) {
			List<Microsoft.SqlServer.Management.Smo.StoredProcedure> smoProcs = new List<Microsoft.SqlServer.Management.Smo.StoredProcedure>();

			// Whip up an anonymous typed List
			var procInDbml = new { SchemaName = "", ProcName = "" };
			var procsInDbml = (new[] { procInDbml }).ToList();
			procsInDbml.RemoveAt(0);

			// Store the schema and proc names of all the procs in the dbml
			foreach (dotNails.CodeGen.Models.Function dbmlProc in dbml.DB.Functions) {
				string schemaName;
				string procName;

				// Dbml mashes the schema and name together, so we need to split them out
				DbmlUtil.GetSchemaAndNameFromFullName(dbmlProc.Name, out schemaName, out procName);
				procsInDbml.Add(new { SchemaName = schemaName, ProcName = procName });
			}
			
			if (dbml.DB != null && dbml.DB.Connection != null && !string.IsNullOrEmpty(dbml.DB.Connection.ConnectionString)) {
				// Get the stored procedures from the database
				SqlConnection conn = new SqlConnection(dbml.DB.Connection.ConnectionString);
				Server server = new Server(new ServerConnection(conn));
				server.SetDefaultInitFields(typeof(Microsoft.SqlServer.Management.Smo.StoredProcedure), "IsSystemObject");

				foreach (Database db in server.Databases) {
					if (db.Name == dbml.DB.Name) {
						// We've got the right database, now filter out any proc already in the dbml
						foreach (Microsoft.SqlServer.Management.Smo.StoredProcedure smoProc in db.StoredProcedures) {
							if (!smoProc.IsSystemObject) {
								var dbmlProc = procsInDbml.Find(p => p.ProcName == smoProc.Name && p.SchemaName == smoProc.Schema);
								if (dbmlProc == null) {
									// Proc not found in dbml, so add it to the list
									smoProcs.Add(smoProc);
								}
							}
						}
					}
				}
			}
			else {
				throw new Exception("Connection string not found for " + dbml.Name);
			}

			return smoProcs;
		}

		public static dotNails.CodeGen.Models.Table SyncDbmlTableWithDatabase(Database smoDb, dotNails.CodeGen.Models.Table dbmlTable) {
			// TODO: Handle TableRelationships that may be pre-existing, yet still needing some data.
			//  - Handle deleting of columns.
			string schemaName;
			string tableName;
			DbmlUtil.GetSchemaAndNameFromFullName(dbmlTable.Name, out schemaName, out tableName);
			Microsoft.SqlServer.Management.Smo.Table smoTable = SmoUtil.GetSmoTable(smoDb, schemaName, tableName);
			dotNails.CodeGen.Models.Table syncedDbmlTable = null;

			// Populate a new dbmlTable straight from the db
			syncedDbmlTable = SmoUtil.GetDbmlTableFromSmoTable(smoTable);

			if (dbmlTable.TableRelationships == null) {
				dbmlTable.TableRelationships = new List<TableRelationship>();
			}

			// Fill in any gaps in the dbml info just read from the db with info from the existing dbml table
			foreach (TableRelationship relationship in syncedDbmlTable.TableRelationships) {
				TableRelationship dbmlRelationship = dbmlTable.TableRelationships.Find(r => r.ForeignKeyName == relationship.ForeignKeyName);
				if (dbmlRelationship != null) {
					relationship.Paired = dbmlRelationship.Paired;
					relationship.ReferencedTable = string.IsNullOrEmpty(relationship.ReferencedTable) ? dbmlRelationship.ReferencedTable : relationship.ReferencedTable;
					relationship.ReferencedTableSchema = string.IsNullOrEmpty(relationship.ReferencedTableSchema) ? dbmlRelationship.ReferencedTableSchema : relationship.ReferencedTableSchema;
					relationship.ReferencedColumn = string.IsNullOrEmpty(relationship.ReferencedColumn) ? dbmlRelationship.ReferencedColumn : relationship.ReferencedColumn;
					relationship.ReferencingTable = string.IsNullOrEmpty(relationship.ReferencingTable) ? dbmlRelationship.ReferencingTable : relationship.ReferencingTable;
					relationship.ReferencingTableSchema = string.IsNullOrEmpty(relationship.ReferencingTableSchema) ? dbmlRelationship.ReferencingTableSchema : relationship.ReferencingTableSchema;
					relationship.ReferencingColumn = string.IsNullOrEmpty(relationship.ReferencingColumn) ? dbmlRelationship.ReferencingColumn : relationship.ReferencingColumn;
				}
				else {
				}
			}

			// Go through and set any user-defined data on the table
			// This is what PLINQO remembers
			//Database/@Class - The name of the DataContext class that will be generated. 
			//Database/@EntityNamespace - The namespace for the entity classes. 
			//Database/@ContextNamespace - The namespace for the DataContext class. 
			//Table/@Member - The property name for the table in the DataContext class. 
			//Type/@Name - The name of the entity class. 
			//Column/@Member - The property name for the column in the entity class. 
			//Column/@Storage - The private field LINQ to SQL will us to assign values to. 
			//Association/@Member - The property name for this association. 
			//Association/@Storage - The private field LINQ to SQL will us to assign values the association to. 
			//Function/@Method  - The name of the method for the database procedure. 
			//Parameter/@Parameter - The method argument name that maps to the database procedure parameter.

			// ??? the Member attribute
			syncedDbmlTable.Member = dbmlTable.Member;
			// Honor any renamed tables
			syncedDbmlTable.Type.Name = dbmlTable.Type.Name;

			foreach (dotNails.CodeGen.Models.Column column in syncedDbmlTable.Type.Columns) {
				dotNails.CodeGen.Models.Column oldColumn = dbmlTable.Type.Columns.Find(c => c.Name == column.Name);

				if (oldColumn != null) {
					// Honor the Member attribute (for renaming columns)
					column.Member = string.IsNullOrEmpty(oldColumn.Member) ? null : oldColumn.Member;
					// Honor the UpdateCheck selection
					column.UpdateCheck = oldColumn.UpdateCheck;
				}
			}

			// Should we also sync all existing dbml tables that reference this table?
			//  I'm going to start out saying no for an Add - every table add would re-sync almost the whole dbml.
			//  On a plain Sync operation though, we should?? sync the tables that reference the tables being synced.
			//  Ideally, we would have a performant diff capability, and always sync any table that has changed (and only the
			//  tables that have changed) on any operation.  I don't see how to do that now, so I'm going to put the 
			//  responsibility on the developer to call for a sync when needed.
			// if (syncReferencingTables) {
			//}

			//// Maintain the associations that are unrelated to the tables being added
			//foreach (Association association in dbmlTable.Type.Associations) {
			//    // Don't add any association that will be created from a paired relationship.  Let those be overwritten.
			//    TableRelationship nonPairedRelationship = syncedDbmlTable.TableRelationships.Find(r => r.Paired && DbmlUtil.GetAssociationName(r) == association.Name);
			//    if (nonPairedRelationship == null) {
			//        // Copy any association over from the existing dbml that isn't affected by a paired relationship (otherwise it will be deleted)
			//        syncedDbmlTable.Type.Associations.Add(association);
			//    }
			//}

			return syncedDbmlTable;
		}

		public static dotNails.CodeGen.Models.Table GetDbmlTableFromSmoTable(Microsoft.SqlServer.Management.Smo.Table smoTable) {
			dotNails.CodeGen.Models.Table dbmlTable = new dotNails.CodeGen.Models.Table();
			dbmlTable.Name = DbmlUtil.BuildDbmlTableName(smoTable.Schema, smoTable.Name);
			dbmlTable.Type = new dotNails.CodeGen.Models.Type();
			dbmlTable.Type.Name = StringUtil.GetPascalCaseName(smoTable.Name);
			dbmlTable.Member = StringUtil.Pluralize(dbmlTable.Type.Name);
			dbmlTable.Type.Columns = new List<dotNails.CodeGen.Models.Column>();
			dbmlTable.Type.Associations = new List<dotNails.CodeGen.Models.Association>();
			dbmlTable.TableRelationships = new List<TableRelationship>();

			// Gather the data we can on foreign keys on this first pass through.  We'll need to pair them up when we're done here.
			// This will handle the keys that we reference on other tables
			foreach (ForeignKey foreignKey in smoTable.ForeignKeys) {
				string referencedTable = foreignKey.ReferencedTable;
				string referencedTableSchema = foreignKey.ReferencedTableSchema;
				foreach (ForeignKeyColumn foreignKeyColumn in foreignKey.Columns) {
					TableRelationship relationship = new TableRelationship {
						IsReferencingTable = true,
						ReferencedTable = referencedTable,
						ReferencedTableSchema = referencedTableSchema,
						ReferencingTable = smoTable.Name,
						ForeignKeyName = foreignKeyColumn.Parent.Name,
						TableName = smoTable.Name,
						Paired = false,
						ReferencedColumn = foreignKeyColumn.ReferencedColumn,
						ReferencingColumn = foreignKeyColumn.Name
					};
					dbmlTable.TableRelationships.Add(relationship);
				}
			}

			// This will handle the keys that other tables reference on us
			System.Data.DataTable foreignKeyInfo = smoTable.EnumForeignKeys();
			foreach (System.Data.DataRow row in foreignKeyInfo.Rows) {
				TableRelationship relationship = new TableRelationship {
					IsReferencingTable = false,
					ReferencedTable = smoTable.Name,
					ReferencingTableSchema = row.ItemArray[0].ToString(),
					ReferencingTable = row.ItemArray[1].ToString(),
					ForeignKeyName = row.ItemArray[2].ToString(),
					TableName = smoTable.Name,
					Paired = false
				};
				dbmlTable.TableRelationships.Add(relationship);
			}

			// Get all the column info
			foreach (Microsoft.SqlServer.Management.Smo.Column dbColumn in smoTable.Columns) {
				dotNails.CodeGen.Models.Column dbmlColumn = new dotNails.CodeGen.Models.Column();
				dbmlColumn.Name = dbColumn.Name;
				dbmlColumn.Type = DbmlUtil.GetDbmlTypeFromDbType(dbColumn.DataType.SqlDataType.ToString(), dbColumn.DataType.MaximumLength.ToString());
				dbmlColumn.DbType = DbmlUtil.GetDbmlDbType(dbColumn.DataType, dbColumn.Nullable, dbColumn.Identity);
				dbmlColumn.IsPrimaryKey = dbColumn.InPrimaryKey;
				dbmlColumn.IsDbGenerated = dbColumn.Identity;
				dbmlColumn.CanBeNull = dbColumn.Nullable;
				dbmlTable.Type.Columns.Add(dbmlColumn);
			}

			return dbmlTable;
		}

		public static dotNails.CodeGen.Models.Function GetDbmlFunctionFromSmoStoredProcedure(Microsoft.SqlServer.Management.Smo.StoredProcedure smoProc, SqlConnection conn, Dictionary<string, string> mappedTableIDs) {
			dotNails.CodeGen.Models.Function dbmlFunction = new Function();

			dbmlFunction.Name = DbmlUtil.BuildDbmlTableName(smoProc.Schema, smoProc.Name);
			dbmlFunction.Method = smoProc.Name;

			foreach (StoredProcedureParameter parameter in smoProc.Parameters) {
				dbmlFunction.Parameters.Add(new dotNails.CodeGen.Models.Parameter {
												Name = parameter.Name.Replace("@", ""),
												ParameterName = StringUtil.GetCamelCaseName(parameter.Name.Replace("@", "")),
												Type = DbmlUtil.GetDbmlTypeFromDbType(parameter.DataType.SqlDataType.ToString(), parameter.DataType.MaximumLength.ToString()),
												DbType = DbmlUtil.GetDbmlDbType(parameter.DataType, true, false),
												Direction = DbmlUtil.GetParameterDirection(parameter.IsOutputParameter) 
											});
			}

			
			// Check to see if this proc has been mapped to an existing Type
			bool isMapped = mappedTableIDs.ContainsKey(smoProc.Name);
			
			if (isMapped) {
				// Get table.Type.ID for the mapped table
				dbmlFunction.ElementType = new ElementType { IdRef = mappedTableIDs[dbmlFunction.Method] };
			}
			else {
				// Get the columns returned in the resultset
				List<DataRow> returnedColumns = GetStoredProcedureResultData(conn, dbmlFunction);

				//<Function Name="dbo.GetProductsByOrderID" Method="GetProductsByOrderID">
				//    <Parameter Name="orderID" Type="System.Int32" DbType="Int" />
				//    <ElementType Name="GetProductsByOrderIDResult">
				//        <Column Name="ProductID" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
				//        <Column Name="Name" Type="System.String" DbType="VarChar(50) NOT NULL" CanBeNull="false" />
				//        <Column Name="Description" Type="System.String" DbType="VarChar(50)" CanBeNull="true" />
				//        <Column Name="Quantity" Type="System.Int32" DbType="Int NOT NULL" CanBeNull="false" />
				//    </ElementType>
				//</Function>
				// Get all the column info
				if (returnedColumns.Count == 0) {
					dbmlFunction.Return = new Return { Type = "System.Int32" }; // no results, but LinqToSql will always return an integer
				}
				else {
					if (returnedColumns.Count == 1 && returnedColumns[0]["ColumnName"].ToString() == "ScalarValue") {
						// There is only one returned column and it's name is "ScalarValue".  Setup our proc to return a scalar value.
						dbmlFunction.Return = new Return { Type = "ISingleResult<ScalarResult<" + DbmlUtil.GetCSharpVariableType(returnedColumns[0]["DataType"].ToString(), false) + ">>" };
					}
					else {
						// Resultset returned - create the result class
						dbmlFunction.ElementType = new ElementType { Name = dbmlFunction.Method + "Result" };

						int currentColCount = 1;
						foreach (DataRow column in returnedColumns) {
							dotNails.CodeGen.Models.Column dbmlColumn = new dotNails.CodeGen.Models.Column();
							dbmlColumn.Name = column["ColumnName"].ToString();
							if (string.IsNullOrEmpty(dbmlColumn.Name)) {
								// if the proc didn't name the column, we have to give it a name here
								dbmlColumn.Name = "Column" + currentColCount++;
							}
							dbmlColumn.Type = column["DataType"].ToString();
							dbmlColumn.DbType = DbmlUtil.GetDbmlDbType(column);
							dbmlColumn.IsPrimaryKey = false; // resultsets don't have primary keys
							dbmlColumn.IsDbGenerated = Convert.ToBoolean(column["AllowDBNull"]);
							dbmlColumn.CanBeNull = Convert.ToBoolean(column["AllowDBNull"]);
							dbmlFunction.ElementType.Columns.Add(dbmlColumn);
						}
					}
				}

				
			}
			
			return dbmlFunction;
		}

		private static List<DataRow> GetStoredProcedureResultData(SqlConnection conn, Function dbmlFunction) {
			List<DataRow> returnedColumns = new List<DataRow>();
			StringBuilder commandText = new StringBuilder();
			commandText.Append("exec ").Append(dbmlFunction.Name);
			bool first = true;
			foreach (dotNails.CodeGen.Models.Parameter parameter in dbmlFunction.Parameters) {
				commandText.Append(first ? " " : ", ");
				commandText.Append(DbmlUtil.GetValidTypeValueForDB(parameter.Type));
				first = false;
			}
			//exec dbo.CreateProduct 1 't' 't' 1
			DataTable resultData = null;
			// conn is assumed to be an open connection to the db
			using (SqlCommand command = new SqlCommand(commandText.ToString(), conn)) {
				SqlDataReader reader = command.ExecuteReader(CommandBehavior.SchemaOnly);
				resultData = reader.GetSchemaTable();
				reader.Close();
			}

			//For each field in the table...
			StringBuilder output = new StringBuilder();
			if (resultData != null) {
				foreach (DataRow column in resultData.Rows) {
					returnedColumns.Add(column);
				}
			}

			return returnedColumns;
		}

	}
}
